<?php
set_include_path($_SERVER ['DOCUMENT_ROOT']);
include_once 'admin/includes/functions/admin-functions.php';
$GLOBALS['DashboardData'] = array();

/**
 * Function is used for calculating the total revenue from the database based on certain parameters
 *
 * @param array $method This option allows one or multiple payment methods to be passed to the function to filter the selected payments
 * @param string $range1 This option allows a start date to be passed to the function to filter the selected payments
 * @param string $range2 This option allows a end date to be passed to the function to filter the selected payments
 * @param string $status This option allows a status to be passed to the function to filter the selected payments
 */
function revenue($method = array(), $range1 = '', $range2 = '', $status = '', $term = '', $year = '', $franshiseAr = array())
{
    global $DBobject,$DashboardData;
  
    if (empty($DashboardData['revenue'.$year.'-'.$term])) {
        $params = array();
        $sql_status="";
        $sql_term="";
        $sql_franchisee="";
    
        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            }
        }
    
        if (!empty($franshiseAr)) {
            $n=1;
            $tmpsql=array();
            $tparams=array();
            foreach ($franshiseAr as $m) {
                $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
                $tparams[":franchisee{$n}"] = $m;
                $n++;
            }
            $tfsql = " AND (".implode(" OR ", $tmpsql).")";
            $sqlfranchisee = "SELECT class_object_id FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id WHERE tbl_class.class_deleted IS NULL AND tbl_class.class_published = 1 AND tbl_location.location_published = 1 AND tbl_location.location_deleted IS NULL {$tfsql}";
            $res_t = $DBobject->wrappedSql($sqlfranchisee, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_class_object_id = {$tr['class_object_id']}";
            }
            $sql_franchisee = " AND (".implode(" OR ", $tmptsql)." )";
        }
    
  
        $sql="SELECT payment_id, payment_total,payment_charged_amount,payment_response_cardscheme, order_status_id,term_region FROM tbl_payment LEFT JOIN tbl_enrolment ON payment_id = enrolment_payment_id LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE payment_deleted IS NULL AND enrolment_published = 1 AND enrolment_deleted IS NULL{$sql_term}{$sql_filters}{$sql_franchisee} ";//GROUP BY payment_id";
        $res = $DBobject->wrappedSql($sql, $params);
        foreach ($res as $e) {
            if (empty($DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"])) {
                $DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"] = array();
            }
            if (empty($DashboardData['revenueregion']["{$e['term_region']}"])) {
                $DashboardData['revenueregion']["{$e['term_region']}"] = array();
            }
            if (empty($DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"])) {
                $DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"] = array();
            }
      
            //Check if a partial Payment
            if (in_array($e['order_status_id'], array(3,5,8))) {
                $DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"] = doubleval($DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"]) + $e['payment_charged_amount'];
                $DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"] = doubleval($DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"]) + $e['payment_charged_amount'];
            } else {
                $DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"] = doubleval($DashboardData['revenue'.$year.'-'.$term]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"]) + $e['payment_total'];
                $DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"] = doubleval($DashboardData['revenueregion']["{$e['term_region']}"]["{$e['payment_response_cardscheme']}"]["{$e['order_status_id']}"]) + $e['payment_total'];
            }
        }
    }

    $rev = 0;
    if (!empty($method)) {
        foreach ($method as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $rev = $rev + $DashboardData['revenue'.$year.'-'.$term]["{$t}"]["{$st}"];
                }
            } else {
                foreach ($DashboardData['revenue'.$year.'-'.$term]["{$t}"] as $st) {
                    $rev = $rev + $st;
                }
            }
        }
    } else {
        foreach ($DashboardData['revenue'.$year.'-'.$term] as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $rev = $rev + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $rev = $rev + $st;
                }
            }
        }
    }
    return $rev;
}

/**
 * This function returns the number of enrolments filtered by parameters. This can show all enrolments as well as paid enrolments
 *
 * @param unknown $type
 * @param string $range1
 * @param string $range2
 * @param string $status
 * @param string $term
 */
function enrolments($type = array(), $range1 = '', $range2 = '', $status = '', $term = '', $year = '', $franshiseAr = array(), $ignoreUnpublished = false)
{
    global $DBobject,$DashboardData;
  
    if (empty($DashboardData['enrolments'.$year.'-'.$term])) {
        $params = array();
        $sql_term="";
        $sql_franchisee="";
    
        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            }
        }
    
        if (!empty($franshiseAr)) {
            $n=1;
            $tmpsql=array();
            $tparams=array();
            foreach ($franshiseAr as $m) {
                $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
                $tparams[":franchisee{$n}"] = $m;
                $n++;
            }
            $tfsql = " AND (".implode(" OR ", $tmpsql).")";
            $sqlfranchisee = "SELECT class_object_id FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id WHERE tbl_class.class_deleted IS NULL AND tbl_class.class_published = 1 AND tbl_location.location_published = 1 AND tbl_location.location_deleted IS NULL {$tfsql}";
            $res_t = $DBobject->wrappedSql($sqlfranchisee, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_class_object_id = {$tr['class_object_id']}";
            }
            $sql_franchisee = " AND (".implode(" OR ", $tmptsql)." )";
        }
   
        $sql_unpublished=$ignoreUnpublished?"":" AND enrolment_published = 1";
        $sql="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment,enrolment_type,order_status_id,term_region FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE enrolment_deleted IS NULL{$sql_unpublished}{$sql_term}{$sql_filters}{$sql_franchisee} GROUP BY enrolment_type,order_status_id,term_region";
        $res = $DBobject->wrappedSql($sql, $params);
        foreach ($res as $e) {
            if (empty($DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"])) {
                $DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"] = array();
            }
            if (empty($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"])) {
                $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"] = array();
            }
            if (empty($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"])) {
                $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"] = array();
            }
      
            $DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
            $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
        }
    }

    $en = 0;
    if (!empty($type)) {
        foreach ($type as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $en = $en + $DashboardData['enrolments'.$year.'-'.$term]["{$t}"]["{$st}"];
                }
            } else {
                foreach ($DashboardData['enrolments'.$year.'-'.$term]["{$t}"] as $st) {
                    $en = $en + $st;
                }
            }
        }
    } else {
        foreach ($DashboardData['enrolments'.$year.'-'.$term] as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $en = $en + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $en = $en + $st;
                }
            }
        }
    }
  
    return $en;
}

/**
 * Return the number of Users currently in the system with warnings.
 */
function warnings()
{
    global $DBobject;
    $params = array();
    $sql="SELECT COUNT(user_id) AS warnings FROM tbl_user WHERE user_warning IS NOT NULL AND user_warning != '' AND user_deleted IS NULL";
    $res = $DBobject->wrappedSql($sql, $params);
    return $res['0']['warnings'];
}

/**
 * Return the number of published locations in the system.
 */
function locations($franshiseAr = array())
{
    global $DBobject;
    $params = array();
    if (!empty($franshiseAr)) {
        $n=1;
        $tmpsql=array();
        foreach ($franshiseAr as $m) {
            $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            $n++;
        }
        $sql_franchisee = " AND (".implode(" OR ", $tmpsql).") ";
    }
  
    $sql="SELECT COUNT(location_object_id) AS locations FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 AND location_listing_id = 9{$sql_franchisee}";
    $res = $DBobject->wrappedSql($sql, $params);
    return $res['0']['locations'];
}

/**
 * Return the number of published classes in the system.
 */
function classes($term = '', $year = '', $franshiseAr = array())
{
    global $DBobject;
    $params = array();
    if (!empty($franshiseAr)) {
        $n=1;
        $tmpsql=array();
        foreach ($franshiseAr as $m) {
            $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            $n++;
        }
        $sql_franchisee = " AND (".implode(" OR ", $tmpsql).") ";
    }
  
//   if(!empty($term) || !empty($year)){
//     $sql_term =" AND class_object_id IN (SELECT classterm_class_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id LEFT JOIN tbl_classterm ON term_id = classterm_term_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"").")";
//     if(!empty($term)){$params[":term"] = $term;}
//     if(!empty($year)){$params[":year"] = $year;}
//   }
    if (!empty($term) || !empty($year)) {
        $sql_term = "".(!empty($term)?" AND term_termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
        if (!empty($term)) {
            $params[":term"]=$term;
        }
        if (!empty($year)) {
            $params[":year"]=$year;
        }
    }

    $sql = "SELECT COUNT(class_object_id) AS classes,SUM(class_size) AS maxsize, AVG(class_size) AS avgsize FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id LEFT JOIN tbl_classterm ON class_object_id = classterm_class_id LEFT JOIN tbl_term ON classterm_term_id = term_id WHERE tbl_class.class_deleted IS NULL AND tbl_location.location_deleted IS NULL AND classterm_deleted IS NULL AND classterm_published = 1 AND location_published = 1{$sql_franchisee}{$sql_term}";
    $res = $DBobject->wrappedSql($sql, $params);
    return $res['0'];
}

/**
 * Return the total target for this franchisees in the passed list
 */
function franchiseetarget($franshiseAr = array())
{
    global $DBobject;
    $params = array();
    if (!empty($franshiseAr)) {
        $n=1;
        $tmpsql=array();
        foreach ($franshiseAr as $m) {
            $tmpsql[] ="franchisee_object_id = :franchisee{$n}";
            $params[":franchisee{$n}"] = $m;
            $n++;
        }
        $sql_franchisee = " AND (".implode(" OR ", $tmpsql).") ";
    }

    $sql="SELECT SUM(franchisee_enrolment_target) AS target FROM tbl_franchisee WHERE franchisee_published = 1 AND franchisee_deleted IS NULL {$sql_franchisee}";
    $res = $DBobject->wrappedSql($sql, $params);
    return $res['0']['target'];
}

/**
 * Return the number of trials which have converted to full fee paying members
 */
function trialconversions($range1 = '', $range2 = '', $status = '', $term = '', $year = '', $franshiseAr = array())
{
    global $DBobject;
    $params = array();
  

    $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
    if (!empty($term) || !empty($year)) {
        $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
        $tparams = array();
        if (!empty($term)) {
            $tparams[":term"]=$term;
        }
        if (!empty($year)) {
            $tparams[":year"]=$year;
        }
        $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
        $tmptsql=array();
        foreach ($res_t as $tr) {
            $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
        }
        if (!empty($tmptsql)) {
            $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
        }
    }
  
    if (!empty($franshiseAr)) {
        $n=1;
        $tmpsql=array();
        $tparams=array();
        foreach ($franshiseAr as $m) {
            $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
            $tparams[":franchisee{$n}"] = $m;
            $n++;
        }
        $tfsql = " AND (".implode(" OR ", $tmpsql).")";
        $sqlfranchisee = "SELECT class_object_id FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id WHERE tbl_class.class_deleted IS NULL AND tbl_class.class_published = 1 AND tbl_location.location_published = 1 AND tbl_location.location_deleted IS NULL {$tfsql}";
        $res_t = $DBobject->wrappedSql($sqlfranchisee, $tparams);
        $tmptsql=array();
        foreach ($res_t as $tr) {
            $tmptsql[]="enrolment_class_object_id = {$tr['class_object_id']}";
        }
        $sql_franchisee = " AND (".implode(" OR ", $tmptsql)." )";
    }

    $sql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id  WHERE enrolment_deleted IS NULL AND enrolment_type = 'T' AND EXISTS (SELECT t_e.enrolment_id FROM tbl_enrolment AS t_e WHERE t_e.enrolment_student_id = tbl_enrolment.enrolment_student_id AND t_e.enrolment_deleted IS NULL AND (t_e.enrolment_type = 'S' OR t_e.enrolment_type = 'MV')){$sql_term}{$sql_filters}{$sql_franchisee}";

    $res = $DBobject->wrappedSql($sql, $params);
    return $res['0']['enrolment'];
}

/**
 * Return the number of students enrolled as a trial
 */
function trials($range1 = '', $range2 = '', $status = '', $term = '', $year = '', $franshiseAr = array(), $ignoreUnpublished = false)
{
    global $DBobject,$DashboardData;
    $type=array('T');
  
    if (empty($DashboardData['trials'])) {
        $params = array();
        $sql_term="";
        $sql_franchisee="";
    
        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            }
        }
    
        if (!empty($franshiseAr)) {
            $n=1;
            $tmpsql=array();
            $tparams=array();
            foreach ($franshiseAr as $m) {
                $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
                $tparams[":franchisee{$n}"] = $m;
                $n++;
            }
            $tfsql = " AND (".implode(" OR ", $tmpsql).")";
            $sqlfranchisee = "SELECT class_object_id FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id WHERE tbl_class.class_deleted IS NULL AND tbl_class.class_published = 1 AND tbl_location.location_published = 1 AND tbl_location.location_deleted IS NULL {$tfsql}";
            $res_t = $DBobject->wrappedSql($sqlfranchisee, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_class_object_id = {$tr['class_object_id']}";
            }
            $sql_franchisee = " AND (".implode(" OR ", $tmptsql)." )";
        }
   
        $sql_unpublished=$ignoreUnpublished?"":" AND enrolment_published = 1";
        $sql="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment,enrolment_type,order_status_id,term_region FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE enrolment_type = 'T' AND enrolment_deleted IS NULL{$sql_unpublished}{$sql_term}{$sql_filters}{$sql_franchisee} GROUP BY enrolment_type,order_status_id,term_region";
        $res = $DBobject->wrappedSql($sql, $params);
        foreach ($res as $e) {
            if (empty($DashboardData['trials']["{$e['enrolment_type']}"])) {
                $DashboardData['trials']["{$e['enrolment_type']}"] = array();
            }
            $DashboardData['trials']["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['trials']["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
        }
    }

    $en = 0;
    if (!empty($type)) {
        foreach ($type as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $en = $en + $DashboardData['trials']["{$t}"]["{$st}"];
                }
            } else {
                foreach ($DashboardData['trials']["{$t}"] as $st) {
                    $en = $en + $st;
                }
            }
        }
    } else {
        foreach ($DashboardData['trials'] as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $en = $en + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $en = $en + $st;
                }
            }
        }
    }
  
    return $en;
}

function reenrolments($range1 = '', $range2 = '', $status = '', $term = '', $year = '', $franshiseAr = array())
{
    global $DBobject,$DashboardData;
    $type = array('S','MV');
  
    if (empty($DashboardData['reenrolments'.$year.'-'.$term])) {
        $params = array();
        $sql_term="";
        $sql_franchisee="";
    
        $sqlyear = "SELECT term_termcategory_id, DATE_FORMAT(term_start_date,'%Y') AS year FROM tbl_term WHERE term_termcategory_id != :term AND term_start_date < (SELECT MIN(tm.term_start_date) FROM tbl_term AS tm WHERE tm.term_termcategory_id = :term AND DATE_FORMAT(tm.term_start_date,'%Y')=:year ) ORDER BY DATE_FORMAT(term_start_date,'%Y%m%d') DESC LIMIT 1";
        $pretmres = $DBobject->wrappedSql($sqlyear, array("term"=>$term,"year"=>$year));
    
        $preterm = $pretmres[0]['term_termcategory_id'];
        $preyear = $pretmres[0]['year'];

        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            }
        }
        $sql_preterm = "";
        if (!empty($preterm) || !empty($preyear)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $terparams = array();
            if (!empty($term)) {
                $terparams[":term"]=$preterm;
            }
            if (!empty($year)) {
                $terparams[":year"]=$preyear;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $terparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_preterm = " AND (".implode(" OR ", $tmptsql)." )";
            } else {
                $data = array("previous"=>0,"reenrolled"=>0);
                return $data;
            }
        }
  
        if (!empty($franshiseAr)) {
            $n=1;
            $tmpsql=array();
            $tparams=array();
            foreach ($franshiseAr as $m) {
                $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
                $tparams[":franchisee{$n}"] = $m;
                $n++;
            }
            $tfsql = " AND (".implode(" OR ", $tmpsql).")";
            $sqlfranchisee = "SELECT class_object_id FROM tbl_class LEFT JOIN tbl_location ON tbl_class.class_location_id = tbl_location.location_object_id WHERE tbl_class.class_deleted IS NULL AND tbl_class.class_published = 1 AND tbl_location.location_published = 1 AND tbl_location.location_deleted IS NULL {$tfsql}";
            $res_t = $DBobject->wrappedSql($sqlfranchisee, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_class_object_id = {$tr['class_object_id']}";
            }
            $sql_franchisee = " AND (".implode(" OR ", $tmptsql)." )";
        }
  
        if (empty($DashboardData['reenrolments'.$year.'-'.$term]['previous'])) {
            $DashboardData['reenrolments'.$year.'-'.$term]['previous'] = array();
        }
        if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous'])) {
            $DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous'] = array();
        }
        $pre_students_sql=array();
    
        $sql="SELECT DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id)) AS enrolment,enrolment_student_id,enrolment_type,order_status_id,term_region FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL{$sql_preterm}{$sql_filters}{$sql_franchisee} ";
        $res = $DBobject->wrappedSql($sql, $params);
        foreach ($res as $e) {
            if (empty($DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$e['enrolment_type']}"])) {
                $DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$e['enrolment_type']}"] = array();
            }
            if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"])) {
                $DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"] = array();
            }
            if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"]["{$e['enrolment_type']}"])) {
                $DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"]["{$e['enrolment_type']}"] = array();
            }
    
            $DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + 1;
            $DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['reenrolmentsregion'.$year.'-'.$term]['previous']["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + 1;
            $pre_students_sql[] = $e['enrolment_student_id'];
        }

        if (empty($DashboardData['reenrolments'.$year.'-'.$term]['current'])) {
            $DashboardData['reenrolments'.$year.'-'.$term]['current'] = array();
        }
        if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['current'])) {
            $DashboardData['reenrolmentsregion'.$year.'-'.$term]['current'] = array();
        }
        $sql="SELECT DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id)) AS enrolment,enrolment_student_id,enrolment_type,order_status_id,term_region FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL{$sql_term}{$sql_filters}{$sql_franchisee} ";

        $res = $DBobject->wrappedSql($sql, $params);
        foreach ($res as $e) {
            if (!in_array($e['enrolment_student_id'], $pre_students_sql)) {
                continue;
            }
            if (empty($DashboardData['reenrolments'.$year.'-'.$term]['current']["{$e['enrolment_type']}"])) {
                $DashboardData['reenrolments'.$year.'-'.$term]['current']["{$e['enrolment_type']}"] = array();
            }
            if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"])) {
                $DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"] = array();
            }
            if (empty($DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"]["{$e['enrolment_type']}"])) {
                $DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"]["{$e['enrolment_type']}"] = array();
            }
    
            $DashboardData['reenrolments'.$year.'-'.$term]['current']["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['reenrolments'.$year.'-'.$term]['current']["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + 1;
            $DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['reenrolmentsregion'.$year.'-'.$term]['current']["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + 1;
        }
    }

  
    $pre = 0;
    $cur = 0;
    if (!empty($type)) {
        foreach ($type as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $pre = $pre + $DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$t}"]["{$st}"];
                    $cur = $cur + $DashboardData['reenrolments'.$year.'-'.$term]['current']["{$t}"]["{$st}"];
                }
            } else {
                foreach ($DashboardData['reenrolments'.$year.'-'.$term]['previous']["{$t}"] as $st) {
                    $pre = $pre + $st;
                }
                foreach ($DashboardData['reenrolments'.$year.'-'.$term]['current']["{$t}"] as $st) {
                    $cur = $cur + $st;
                }
            }
        }
    } else {
        foreach ($DashboardData['reenrolments'.$year.'-'.$term]['previous'] as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $pre = $pre + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $pre = $pre + $st;
                }
            }
        }
        foreach ($DashboardData['reenrolments'.$year.'-'.$term]['current'] as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $cur = $cur + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $cur = $cur + $st;
                }
            }
        }
    }

    $data = array("previous"=>$pre,"reenrolled"=>$cur);
    return $data;
}

/**
 * This function returns the number of enrolments filtered by parameters. This can show all enrolments as well as paid enrolments
 *
 * @param unknown $type
 * @param string $range1
 * @param string $range2
 * @param string $status
 * @param string $term
 */
function enrolmentbystate($type = array(), $range1 = '', $range2 = '', $status = '', $term = '', $year = '')
{
    global $DBobject,$DashboardData;
    $results = array();
    $states = $DBobject->wrappedSql("SELECT DISTINCT(term_region) FROM tbl_term WHERE term_deleted IS NULL");
  
    if (empty($DashboardData['enrolmentsregion'.$year.'-'.$term])) {
        $params = array();
        $sql_term="";
        $sql_franchisee="";
    
        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            } else {
                foreach ($states as $row) {
                    $results[] = array("term_region"=>$row['term_region'],"enrolment"=>0);
                }
                return $results;
            }
        }
    
        $sql="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment,enrolment_type,order_status_id,term_region FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_term ON enrolment_term_object_id = term_object_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL{$sql_term}{$sql_filters}{$sql_franchisee} GROUP BY enrolment_type,order_status_id,term_region";

        $res = $DBobject->wrappedSql($sql, $params);
    
        foreach ($res as $e) {
            if (empty($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"])) {
                $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"] = array();
            }
            if (empty($DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"])) {
                $DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"] = array();
            }
            if (empty($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"])) {
                $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"] = array();
            }
      
            $DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['enrolments'.$year.'-'.$term]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
            $DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['enrolmentsregion'.$year.'-'.$term]["{$e['term_region']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
        }
    }

    $st_en = array();
    if (!empty($type)) {
        foreach ($type as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    foreach ($DashboardData['enrolmentsregion'.$year.'-'.$term] as $key => $values) {
                        $st_en["$key"] = intval($st_en["$key"]) + $values["{$t}"]["{$st}"];
                    }
                }
            } else {
                foreach ($DashboardData['enrolmentsregion'.$year.'-'.$term] as $key => $values) {
                    foreach ($values as $st) {
                        $st_en["$key"] = intval($st_en["$key"]) + $st;
                    }
                }
            }
        }
    } else {
        foreach ($DashboardData['enrolmentsregion'.$year.'-'.$term] as $key => $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $st_en["$key"] = intval($st_en["$key"]) + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $st_en["$key"] = intval($st_en["$key"]) + $t["{$st}"];
                }
            }
        }
    }

    foreach ($states as $row) {
        if (empty($st_en["{$row['term_region']}"])) {
            $results[] = array("term_region"=>$row['term_region'],"enrolment"=>0);
        } else {
            $results[] = array("term_region"=>$row['term_region'],"enrolment"=>$st_en["{$row['term_region']}"]);
        }
    }

  
    return $results;
}

/**
 * This function returns the number of enrolments filtered by parameters. This can show all enrolments as well as paid enrolments
 *
 * @param unknown $type
 * @param string $range1
 * @param string $range2
 * @param string $status
 * @param string $term
 */
function enrolmentbylocations($type = array(), $status = '', $term = '', $year = '', $franshiseAr = array())
{
    global $DBobject,$DashboardData;
    $results = array();
  
    if (!empty($franshiseAr)) {
        $n=1;
        $tmpsql=array();
        $tparams=array();
        foreach ($franshiseAr as $m) {
            $tmpsql[] ="location_franchisee_id = :franchisee{$n}";
            $tparams[":franchisee{$n}"] = $m;
            $n++;
        }
        $tfsql = " AND (".implode(" OR ", $tmpsql).")";
    }
    $lsql = "SELECT location_id, location_name FROM tbl_location WHERE location_deleted IS NULL AND location_published = 1 {$tfsql}";
    $locations = $DBobject->wrappedSql($lsql, $tparams);
  
    if (empty($DashboardData['enrolmentslocations'.$year.'-'.$term])) {
        $params = array();
        $sql_term="";
        $sql_franchisee="";
  
        $sql_filters=" AND order_deleted IS NULL AND order_status_id != 6 AND order_status_id != 10 AND order_status_id != 9";
        if (!empty($term) || !empty($year)) {
            $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL ".(!empty($term)?" AND termcategory_id = :term ":"")." AND term_deleted IS NULL AND term_published = 1".(!empty($year)?" AND DATE_FORMAT(term_start_date,'%Y') = :year":"");
            $tparams = array();
            if (!empty($term)) {
                $tparams[":term"]=$term;
            }
            if (!empty($year)) {
                $tparams[":year"]=$year;
            }
            $res_t = $DBobject->wrappedSql($sqlterm, $tparams);
            $tmptsql=array();
            foreach ($res_t as $tr) {
                $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
            }
            if (!empty($tmptsql)) {
                $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
            } else {
                foreach ($locations as $row) {
                    $results[] = array("location_name"=>$row['location_name'],"enrolment"=>0);
                }
                return $results;
            }
        }
  
        $sql="SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment,enrolment_type,order_status_id,location_name FROM tbl_enrolment LEFT JOIN tbl_order ON enrolment_payment_id = order_payment_id LEFT JOIN tbl_class ON enrolment_class_object_id = class_object_id LEFT JOIN tbl_location ON class_location_id = location_object_id WHERE enrolment_published = 1 AND enrolment_deleted IS NULL{$sql_term}{$sql_filters}{$sql_franchisee} GROUP BY enrolment_type,order_status_id,location_name";
  
        $res = $DBobject->wrappedSql($sql, $params);
  
        foreach ($res as $e) {
            if (empty($DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"])) {
                $DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"] = array();
            }
            if (empty($DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"]["{$e['enrolment_type']}"])) {
                $DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"]["{$e['enrolment_type']}"] = array();
            }
  
            $DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"] = intval($DashboardData['enrolmentslocations'.$year.'-'.$term]["{$e['location_name']}"]["{$e['enrolment_type']}"]["{$e['order_status_id']}"]) + $e['enrolment'];
        }
    }
  
    $st_en = array();
    if (!empty($type)) {
        foreach ($type as $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    foreach ($DashboardData['enrolmentslocations'.$year.'-'.$term] as $key => $values) {
                        $st_en["$key"] = intval($st_en["$key"]) + $values["{$t}"]["{$st}"];
                    }
                }
            } else {
                foreach ($DashboardData['enrolmentslocations'.$year.'-'.$term] as $key => $values) {
                    foreach ($values as $st) {
                        $st_en["$key"] = intval($st_en["$key"]) + $st;
                    }
                }
            }
        }
    } else {
        foreach ($DashboardData['enrolmentslocations'.$year.'-'.$term] as $key => $t) {
            if (!empty($status)) {
                foreach ($status as $st) {
                    $st_en["$key"] = intval($st_en["$key"]) + $t["{$st}"];
                }
            } else {
                foreach ($t as $st) {
                    $st_en["$key"] = intval($st_en["$key"]) + $t["{$st}"];
                }
            }
        }
    }
  
    foreach ($locations as $row) {
        if (empty($st_en["{$row['location_name']}"])) {
            $results[] = array("location_name"=>$row['location_name'],"enrolment"=>0);
        } else {
            $results[] = array("location_name"=>$row['location_name'],"enrolment"=>$st_en["{$row['location_name']}"]);
        }
    }
  
    return $results;
}

function budget($year, $category)
{
    global $DBobject;
    $sql = "SELECT term_budget AS budget, term_region FROM tbl_term WHERE term_deleted IS NULL AND term_published = 1 AND DATE_FORMAT(term_start_date,'%Y') = :year AND term_termcategory_id = :category";
    $res = $DBobject->wrappedSql($sql, array("year"=>$year,"category"=>$category));
    return $res;
}

function dailyEnrolments()
{
    global $DBobject;

    $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL AND term_deleted IS NULL AND term_published = 1 AND (DATE_FORMAT(term_start_date,'%Y') = DATE_FORMAT(NOW(),'%Y') OR DATE_FORMAT(term_start_date,'%Y') = DATE_FORMAT(SUBDATE(NOW(),'90 days'),'%Y'))";
    $res_t = $DBobject->wrappedSql($sqlterm);
    $tmptsql=array();
    foreach ($res_t as $tr) {
        $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
    }
    if (!empty($tmptsql)) {
        $sql_term = " AND (".implode(" OR ", $tmptsql)." )";
    }
  
    $sqlterm = "SELECT term_object_id FROM tbl_termcategory LEFT JOIN tbl_term ON termcategory_id = term_termcategory_id WHERE termcategory_deleted IS NULL AND (DATE_FORMAT(term_start_date,'%Y') = DATE_FORMAT(SUBDATE(NOW(),INTERVAL 1 YEAR) ,'%Y') OR DATE_FORMAT(term_start_date,'%Y') = DATE_FORMAT(SUBDATE(SUBDATE(NOW(),INTERVAL 1 YEAR) ,'90 days'),'%Y'))";
    $res_t = $DBobject->wrappedSql($sqlterm);
    $tmptsql=array();
    foreach ($res_t as $tr) {
        $tmptsql[]="enrolment_term_object_id = {$tr['term_object_id']}";
    }
    if (!empty($tmptsql)) {
        $sql_terms_pre = " AND (".implode(" OR ", $tmptsql)." )";
    }

  
    $sql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment, DATE_FORMAT(payment_created,'%m-%d') AS day FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id	= payment_id WHERE enrolment_published = 1 AND (enrolment_type='S' OR enrolment_type = 'MV') AND enrolment_deleted IS NULL AND payment_deleted IS NULL AND payment_status = 'A' AND DATE_FORMAT(payment_created,'%Y-%m-%d') > DATE_FORMAT(SUBDATE(NOW(),'90 days'),'%Y-%m-%d') {$sql_term} GROUP BY enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id, payment_created";
    $res = $DBobject->wrappedSql($sql);
    $current = array();
    foreach ($res as $r) {
        $current["{$r['day']}"] = $r['enrolment'];
    }
  
    $sql = "SELECT COUNT(DISTINCT(CONCAT(enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id))) AS enrolment, DATE_FORMAT(payment_created,'%m-%d') AS day FROM tbl_enrolment LEFT JOIN tbl_payment ON enrolment_payment_id = payment_id WHERE enrolment_published = 1 AND (enrolment_type='S' OR enrolment_type = 'MV') AND enrolment_deleted IS NULL AND payment_deleted IS NULL AND payment_status = 'A' AND DATE_FORMAT(payment_created,'%Y-%m-%d') <=DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR),'%Y-%m-%d') AND DATE_FORMAT(payment_created,'%Y-%m-%d') > DATE_FORMAT(DATE_SUB(DATE_SUB(NOW(),INTERVAL 1 YEAR),INTERVAL 90 DAY),'%Y-%m-%d') {$sql_terms_pre} GROUP BY enrolment_student_id, enrolment_class_object_id, enrolment_type, enrolment_term_object_id, payment_created";
    $res2 = $DBobject->wrappedSql($sql);
    $previous = array();
    foreach ($res2 as $r) {
        $previous["{$r['day']}"] = $r['enrolment'];
    }

  //loop last 90 days and build array();
    $array = array();
    for ($i = 0; $i <=90; $i++) {
        $dateB = new DateTime();
        $dateB = $dateB->sub(date_interval_create_from_date_string("{$i} days"));
        $key = $dateB->format('m-d');
    
        $c=0;
        $p=0;
        if (!empty($current["{$key}"])) {
            $c=$current["{$key}"];
        }
        if (!empty($previous["{$key}"])) {
            $p=$previous["{$key}"];
        }
        $array[] = array("current"=>$c,"previous"=>$p);
    }

    return $array;
}
